﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Scheduling.Models.Interfaces;
using Scheduling.Models.Repos.Database.Objects;
using Scheduling.Models.Helpers;
using Scheduling.Models.Extensions;
using System.Globalization;
namespace Scheduling.Models.Repos.Database
{
    public class DatabaseBookingRequestLoader : IBookingRequestLoader
    {
        public List<BookingRequest> GetBookingRequests()
        {
            var db = DBContext.Create();
            var q = from b in db.BookingRequests
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join u in db.Users on b.BookerID equals u.ID
                    orderby b.BookerID
                    select new {b,ci,u};

            if (q.Count() == 0)
                return null;

            List<BookingRequest> requests=LinqHelper.GenericMap<BookingRequest>(q, "b", "ci>CourseInstance", "u>Booker");
            foreach (var request in requests)
            {
                var q2 = from brd in db.BookingRequestDays where brd.BookingRequestID == request.ID select brd;
                request.AllowedDays = new Dictionary<DayOfWeek, DayType>();
                foreach (var d in q2)
                    request.AllowedDays.Add((DayOfWeek)d.Day, (DayType)d.Type);
            }

            return requests;
        }

        public BookingRequest GetBookingRequest(int requestID)
        {
            var db = DBContext.Create();
            var q = from b in db.BookingRequests
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join u in db.Users on b.BookerID equals u.ID
                    where (b.ID == requestID)
                    orderby b.BookerID
                    select new { b, ci, u };

            if (q.Count() == 0)
                return null;

            BookingRequest result = LinqHelper.GenericMap<BookingRequest>(q, "b", "ci>CourseInstance", "u>Booker").First();

            var q2 = from brd in db.BookingRequestDays where brd.BookingRequestID == result.ID select brd;
            result.AllowedDays = new Dictionary<DayOfWeek, DayType>();
            foreach (var d in q2)
                result.AllowedDays.Add((DayOfWeek)d.Day, (DayType)d.Type);

            return result;
        }

        public List<BookingRequest> GetBookingRequestsByUserId(string userID)
        {
            var db = DBContext.Create();
            var q = from b in db.BookingRequests
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join u in db.Users on b.BookerID equals u.ID
                    where (b.BookerID == userID)
                    orderby b.BookerID
                    select new { b, ci, u };

            if (q.Count() == 0)
                return null;

                List<BookingRequest> requests = LinqHelper.GenericMap<BookingRequest>(q, "b", "ci>CourseInstance", "u>Booker");

                foreach (var request in requests)
                {
                    var q2 = from brd in db.BookingRequestDays where brd.BookingRequestID == request.ID select brd;
                    request.AllowedDays = new Dictionary<DayOfWeek, DayType>();
                    foreach (var d in q2)
                        request.AllowedDays.Add((DayOfWeek)d.Day, (DayType)d.Type);
                }
                return requests;
        }

        public void InsertBookingRequest(BookingRequest request)
        {
            var db = DBContext.Create();
            var brL = IoC.Resolve<IBookingRequestLoader>();
            db.ExecuteCommand(
                string.Format("INSERT INTO BookingRequests(CourseInstanceID, BookerID, Time, Year, StartPeriod, RoomType, RoomPattern, RequiredCapacity, Priority, LectureLength, LectureCount, PeriodCount) VALUES({0}, '{1}', {2}, {3}, {4}, {5}, '{6}', {7}, {8}, {9}, {10}, {11})",
                request.CourseInstance.ID, request.Booker.ID, request.Time, request.Year, request.StartPeriod, (int)request.RoomType, request.RoomPattern, request.RequiredCapacity, (int)request.Priority, request.LectureLength, request.LectureCount, 1));
            int id = GetHighestRequestID();
            foreach (KeyValuePair<DayOfWeek, DayType> pair in request.AllowedDays)
            {
                db.ExecuteCommand(
                    string.Format("INSERT INTO BookingRequestDays(BookingRequestID, Day, Type) VALUES({0}, {1}, {2})",
                    id, (int)pair.Key, (int)pair.Value));
            }
            
        }


        /// <summary>
        /// Fetches the highest ID from the BookingRequest table.
        /// </summary>
        /// <returns></returns>

        public int GetHighestRequestID()
        {
            var db = DBContext.Create();
            var result = from p in db.BookingRequests select p;
            if(result.ToList().Last() != null)
                return result.ToList().Last().ID;
            return -1;
        }

        public void DeleteRequest(int ID)
        {
            var db = DBContext.Create();
            db.ExecuteCommand(
                string.Format("DELETE FROM BookingRequests WHERE ID ={0}",ID));
            db.ExecuteCommand(
                string.Format("DELETE FROM BookingRequestDays WHERE BookingRequestID ={0}", ID));
        }
    }
}